﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using TrustX.Dados;
using AcadPos.Entity;
using System.Data.SqlClient;

namespace AcadPos.Data
{
    public class DBAluno
    {
        #region Listar Alunos x Estado
        public DataTable listarAlunosXEstado(string pNome, int pCodEstado)
        {
            #region Script
            string SQL = @"SELECT ta.MATRICULA,
                                   ta.ID_ESTADO,
                                   ta.NOME,
                                   ta.CPF,
                                   ta.RG,
                                   ta.ORGAO_EXPEDITOR_RG,
                                   ta.UF_RG,
                                   ta.DATA_EXPEDICAO_RG,
                                   ta.NACIONALIDADE,
                                   ta.NATURALIDADE,
                                   ta.UF_NATURALIDADE,
                                   ta.GENERO,
                                   ta.ESTADO_CIVIL,
                                   ta.EMAIL,
                                   ta.DDD_TELEFONE,
                                   ta.TELEFONE,
                                   ta.DDD_CELULAR,
                                   ta.CELULAR,
                                   ta.PAI,
                                   ta.MAE,
                                   ta.ENDERECO,
                                   ta.NUMERO,
                                   ta.COMPLEMENTO,
                                   ta.BAIRRO,
                                   ta.UF_ENDERECO,
                                   ta.CIDADE,
                                   ta.PROFISSAO,                                   
                                   ta.SITUACAO,                                   
                                   ISNULL((SELECT top 1 tf.SITUACAO + ' - ' + tc2.DESCRICAO AS ultima_solicitacao
									FROM   TB_FINANCEIRO tf
										   INNER JOIN TB_TURMAS tt
												ON  tt.ID_TURMA = tf.ID_TURMA
										   INNER JOIN TB_CURRICULO tc
												ON  tc.ID_CURRICULO = tt.ID_CURRICULO
										   INNER JOIN TB_CURSO tc2
												ON  tc2.ID_CURSO = tc.ID_CURSO
									WHERE tf.MATRICULA = TA.MATRICULA AND tf.PARCELA = 1
									ORDER BY tf.DATA_VENCIMENTO DESC),'-') AS ultima_solicitacao
                            FROM   TB_ALUNO ta
                            WHERE  
                             ta.NOME LIKE '%' + @NOME + '%' ";
            if (pCodEstado > 0)
                SQL += " AND ta.ID_ESTADO = @ID_ESTADO ";
            SQL += "            ORDER BY ta.NOME";
            #endregion
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@NOME", SqlDbType.VarChar, pNome);
            if (pCodEstado > 0)
                db.AddParam("@ID_ESTADO", SqlDbType.Int, pCodEstado);
            return db.GetDataTable();
        }
        #endregion

        #region Get Dados Pessoais Aluno
        public DataTable getDadosPessoaisAluno(int pMatricula)
        {
            string SQL = @"SELECT ta.MATRICULA,
                                   ta.NOME,
                                   ta.CPF,
                                   ta.RG,
                                   ta.ORGAO_EXPEDITOR_RG,
                                   ta.UF_RG,
                                   ta.DATA_EXPEDICAO_RG,
                                   ta.NACIONALIDADE,
                                   ta.NATURALIDADE,
                                   ta.UF_NATURALIDADE,
                                   CASE ta.GENERO
                                        WHEN 'F' THEN 'FEMININO'
                                        WHEN 'M' THEN 'MASCULINO'
                                   END AS GENERO,
                                   ta.ESTADO_CIVIL,
                                   ta.EMAIL,
                                   ta.DDD_TELEFONE,
                                   ta.TELEFONE,
                                   ta.DDD_CELULAR,
                                   ta.CELULAR,
                                   ta.PAI,
                                   ta.MAE,
                                   ta.ENDERECO,
                                   ta.CEP,
                                   ta.NUMERO,
                                   ta.COMPLEMENTO,
                                   ta.BAIRRO,
                                   ta.UF_ENDERECO,
                                   ta.CIDADE,
                                   ta.PROFISSAO,
                                   tat.DESCRICAO_TITULACAO,
                                   tat.ANO_CONCLUSAO,
                                   tat.INSTITUICAO,
                                   tt.COD_TURMA AS TURMA,
                                   tc2.DESCRICAO AS CURSO,
                                   CONVERT(VARCHAR, tt.DATA_FIM, 103) AS DATA_FIM,
                                   CONVERT(NUMERIC(15, 2), tt.VALOR) AS VALOR,
                                   tt.QTD_PARCELAS
                            FROM   TB_ALUNO ta                                   
                                   LEFT JOIN TB_ALUNO_TITULACAO tat
                                        ON  ta.MATRICULA = tat.MATRICULA
                                   LEFT JOIN TB_MATRICULA tm
                                        ON  tm.MATRICULA = ta.MATRICULA
                                   LEFT JOIN TB_TURMAS tt
                                        ON  tt.ID_TURMA = tm.ID_TURMA
                                   LEFT JOIN TB_CURRICULO tc
                                        ON  tc.ID_CURRICULO = tt.ID_CURRICULO
                                   LEFT JOIN TB_CURSO tc2
                                        ON  tc2.ID_CURSO = tc.ID_CURSO
                                            WHERE  ta.MATRICULA = @MATRICULA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, pMatricula);
            return db.GetDataTable();
        }
        #endregion

        #region Inserir Aluno
        public bool inserirAluno(Aluno aluno, string usuario)
        {
            #region Script
            string SQL = @"INSERT INTO TB_ALUNO
                          (   
                            ID_ESTADO,
                            NOME,
                            DATA_NASCIMENTO,
                            CPF,
                            RG,
                            ORGAO_EXPEDITOR_RG,
                            UF_RG,
                            DATA_EXPEDICAO_RG,
                            NACIONALIDADE,
                            NATURALIDADE,
                            UF_NATURALIDADE,
                            GENERO,
                            ESTADO_CIVIL,
                            EMAIL,
                            DDD_TELEFONE,
                            TELEFONE,
                            DDD_CELULAR,
                            CELULAR,
                            PAI,
                            MAE,
                            ENDERECO,
                            CEP,
                            NUMERO,
                            COMPLEMENTO,
                            BAIRRO,
                            UF_ENDERECO,
                            CIDADE,
                            PROFISSAO,
                            --CURSO_GRADUACAO,
                            --ANO_CONCLUSAO,
                            --INSTITUICAO,
                            SITUACAO,
                            DATA_CADASTRO
                          )
                        VALUES
                          (
                            @ID_ESTADO,
                            @NOME,
                            @DATA_NASCIMENTO,
                            @CPF,
                            @RG,
                            @ORGAO_EXPEDITOR_RG,
                            @UF_RG,
                            @DATA_EXPEDICAO_RG,
                            @NACIONALIDADE,
                            @NATURALIDADE,
                            @UF_NATURALIDADE,
                            @GENERO,
                            @ESTADO_CIVIL,
                            @EMAIL,
                            @DDD_TELEFONE,
                            @TELEFONE,
                            @DDD_CELULAR,
                            @CELULAR,
                            @PAI,
                            @MAE,
                            @ENDERECO,
                            @CEP,
                            @NUMERO,
                            @COMPLEMENTO,
                            @BAIRRO,
                            @UF_ENDERECO,
                            @CIDADE,
                            @PROFISSAO,
                            --@CURSO_GRADUACAO,
                            --@ANO_CONCLUSAO,
                            --@INSTITUICAO,
                            @SITUACAO,
                            GETDATE()
                          )";
            #endregion
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_ESTADO", SqlDbType.Int, aluno.CodEstado);
            db.AddParam("@NOME", SqlDbType.VarChar, aluno.Nome);
            db.AddParam("@CPF", SqlDbType.VarChar, aluno.Cpf);
            db.AddParam("@RG", SqlDbType.VarChar, aluno.Rg);
            db.AddParam("@ORGAO_EXPEDITOR_RG", SqlDbType.VarChar, aluno.OrgaoExpedidor);
            db.AddParam("@UF_RG", SqlDbType.VarChar, aluno.UfRg);
            db.AddParam("@DATA_EXPEDICAO_RG", SqlDbType.DateTime, aluno.DataExpedicaoRg);
            db.AddParam("@DATA_NASCIMENTO", SqlDbType.DateTime, aluno.DataNascimento); 
            db.AddParam("@NACIONALIDADE", SqlDbType.VarChar, aluno.Nacionalidade);
            db.AddParam("@NATURALIDADE", SqlDbType.VarChar, aluno.Naturalidade);
            db.AddParam("@UF_NATURALIDADE", SqlDbType.VarChar, aluno.UfNaturalidade);
            db.AddParam("@GENERO", SqlDbType.VarChar, aluno.Genero);
            db.AddParam("@ESTADO_CIVIL", SqlDbType.VarChar, aluno.EstadoCivil);
            db.AddParam("@EMAIL", SqlDbType.VarChar, aluno.Email);
            db.AddParam("@DDD_TELEFONE", SqlDbType.VarChar, aluno.DddTel);
            db.AddParam("@TELEFONE", SqlDbType.VarChar, aluno.Telefone);
            db.AddParam("@DDD_CELULAR", SqlDbType.VarChar, aluno.Dddcel);
            db.AddParam("@CELULAR", SqlDbType.VarChar, aluno.Celular);
            db.AddParam("@PAI", SqlDbType.VarChar, aluno.Pai);
            db.AddParam("@MAE", SqlDbType.VarChar, aluno.Mae);
            db.AddParam("@ENDERECO", SqlDbType.VarChar, aluno.Endereco);
            db.AddParam("@CEP", SqlDbType.VarChar, aluno.Cep);
            db.AddParam("@NUMERO", SqlDbType.Int, aluno.Numero);
            db.AddParam("@COMPLEMENTO", SqlDbType.VarChar, aluno.Complemento);
            db.AddParam("@BAIRRO", SqlDbType.VarChar, aluno.Bairro);
            db.AddParam("@UF_ENDERECO", SqlDbType.VarChar, aluno.UfEndereco);
            db.AddParam("@CIDADE", SqlDbType.VarChar, aluno.Cidade);
            db.AddParam("@PROFISSAO", SqlDbType.VarChar, aluno.Profissao);
            //db.AddParam("@CURSO_GRADUACAO", SqlDbType.VarChar, aluno.CursoGraduacao);
            //db.AddParam("@ANO_CONCLUSAO", SqlDbType.Int, aluno.AnoConclusao);
            //db.AddParam("@INSTITUICAO", SqlDbType.VarChar, aluno.Instituicao);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, aluno.Situacao);
            //return db.ExecuteNonQuery();

            bool inseriu = db.ExecuteNonQuery();

            if (inseriu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'INSERT',
                        'TB_ALUNO',
                        ((SELECT CAST(MAX(MATRICULA) AS VARCHAR) FROM TB_ALUNO) 
                        + ' | ' + CAST(@ID_ESTADO AS VARCHAR)
                        + ' | ' + UPPER(@NOME)
                        + ' | ' + CONVERT(VARCHAR, @DATA_NASCIMENTO, 103)
                        + ' | ' + UPPER(@CPF)
                        + ' | ' + UPPER(@RG)
                        + ' | ' + UPPER(@ORGAO_EXPEDITOR_RG)
                        + ' | ' + UPPER(@UF_RG)
                        + ' | ' + UPPER(@DATA_EXPEDICAO_RG)
                        + ' | ' + UPPER(@EMAIL)
                        + ' | ' + UPPER(@PAI)
                        + ' | ' + UPPER(@MAE)),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@ID_ESTADO", SqlDbType.Int, aluno.CodEstado);
                db.AddParam("@NOME", SqlDbType.VarChar, aluno.Nome);
                db.AddParam("@DATA_NASCIMENTO", SqlDbType.DateTime, aluno.DataNascimento); 
                db.AddParam("@CPF", SqlDbType.VarChar, aluno.Cpf);
                db.AddParam("@RG", SqlDbType.VarChar, aluno.Rg);
                db.AddParam("@ORGAO_EXPEDITOR_RG", SqlDbType.VarChar, aluno.OrgaoExpedidor);
                db.AddParam("@UF_RG", SqlDbType.VarChar, aluno.UfRg);                
                db.AddParam("@DATA_EXPEDICAO_RG", SqlDbType.DateTime, aluno.DataExpedicaoRg);
                db.AddParam("@EMAIL", SqlDbType.VarChar, aluno.Email);                
                db.AddParam("@PAI", SqlDbType.VarChar, aluno.Pai);
                db.AddParam("@MAE", SqlDbType.VarChar, aluno.Mae);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return inseriu;
        }
        #endregion

        #region Alterar Aluno
        public bool alterarAluno(Aluno aluno, string usuario)
        {
            #region Script
            string SQL = @"UPDATE TB_ALUNO
                            SET    ID_ESTADO = @ID_ESTADO,
                                   NOME = @NOME,
                                   CPF = @CPF,
                                   RG = @RG,
                                   ORGAO_EXPEDITOR_RG = @ORGAO_EXPEDITOR_RG,
                                   UF_RG = @UF_RG,
                                   DATA_EXPEDICAO_RG = @DATA_EXPEDICAO_RG,
                                   DATA_NASCIMENTO = @DATA_NASCIMENTO,
                                   NACIONALIDADE = @NACIONALIDADE,
                                   NATURALIDADE = @NATURALIDADE,
                                   UF_NATURALIDADE = @UF_NATURALIDADE,
                                   GENERO = @GENERO,
                                   ESTADO_CIVIL = @ESTADO_CIVIL,
                                   EMAIL = @EMAIL,
                                   DDD_TELEFONE = @DDD_TELEFONE,
                                   TELEFONE = @TELEFONE,
                                   DDD_CELULAR = @DDD_CELULAR,
                                   CELULAR = @CELULAR,
                                   PAI = @PAI,
                                   MAE = @MAE,
                                   ENDERECO = @ENDERECO,
                                   CEP = @CEP,
                                   NUMERO = @NUMERO,
                                   COMPLEMENTO = @COMPLEMENTO,
                                   BAIRRO = @BAIRRO,
                                   UF_ENDERECO = @UF_ENDERECO,
                                   CIDADE = @CIDADE,
                                   PROFISSAO = @PROFISSAO,
                                   --CURSO_GRADUACAO = @CURSO_GRADUACAO,
                                   --ANO_CONCLUSAO = @ANO_CONCLUSAO,
                                   --INSTITUICAO = @INSTITUICAO,
                                   SITUACAO = @SITUACAO
                            WHERE  MATRICULA = @MATRICULA";
            #endregion
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, aluno.Matricula);
            db.AddParam("@ID_ESTADO", SqlDbType.Int, aluno.CodEstado);
            db.AddParam("@NOME", SqlDbType.VarChar, aluno.Nome);
            db.AddParam("@CPF", SqlDbType.VarChar, aluno.Cpf);
            db.AddParam("@RG", SqlDbType.VarChar, aluno.Rg);
            db.AddParam("@ORGAO_EXPEDITOR_RG", SqlDbType.VarChar, aluno.OrgaoExpedidor);            
            db.AddParam("@UF_RG", SqlDbType.VarChar, aluno.UfRg);
            db.AddParam("@DATA_EXPEDICAO_RG", SqlDbType.DateTime, aluno.DataExpedicaoRg);
            db.AddParam("@DATA_NASCIMENTO", SqlDbType.DateTime, aluno.DataNascimento); 
            db.AddParam("@NACIONALIDADE", SqlDbType.VarChar, aluno.Nacionalidade);
            db.AddParam("@NATURALIDADE", SqlDbType.VarChar, aluno.Naturalidade);
            db.AddParam("@UF_NATURALIDADE", SqlDbType.VarChar, aluno.UfNaturalidade);
            db.AddParam("@GENERO", SqlDbType.VarChar, aluno.Genero);
            db.AddParam("@ESTADO_CIVIL", SqlDbType.VarChar, aluno.EstadoCivil);
            db.AddParam("@EMAIL", SqlDbType.VarChar, aluno.Email);
            db.AddParam("@DDD_TELEFONE", SqlDbType.VarChar, aluno.DddTel);
            db.AddParam("@TELEFONE", SqlDbType.VarChar, aluno.Telefone);
            db.AddParam("@DDD_CELULAR", SqlDbType.VarChar, aluno.Dddcel);
            db.AddParam("@CELULAR", SqlDbType.VarChar, aluno.Celular);
            db.AddParam("@PAI", SqlDbType.VarChar, aluno.Pai);
            db.AddParam("@MAE", SqlDbType.VarChar, aluno.Mae);
            db.AddParam("@ENDERECO", SqlDbType.VarChar, aluno.Endereco);
            db.AddParam("@CEP", SqlDbType.VarChar, aluno.Cep);
            db.AddParam("@NUMERO", SqlDbType.Int, aluno.Numero);
            db.AddParam("@COMPLEMENTO", SqlDbType.VarChar, aluno.Complemento);
            db.AddParam("@BAIRRO", SqlDbType.VarChar, aluno.Bairro);
            db.AddParam("@UF_ENDERECO", SqlDbType.VarChar, aluno.UfEndereco);
            db.AddParam("@CIDADE", SqlDbType.VarChar, aluno.Cidade);
            db.AddParam("@PROFISSAO", SqlDbType.VarChar, aluno.Profissao);
            //db.AddParam("@CURSO_GRADUACAO", SqlDbType.VarChar, aluno.CursoGraduacao);
            //db.AddParam("@ANO_CONCLUSAO", SqlDbType.Int, aluno.AnoConclusao);
            //db.AddParam("@INSTITUICAO", SqlDbType.VarChar, aluno.Instituicao);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, aluno.Situacao);
            //return db.ExecuteNonQuery();

            bool atualizou = db.ExecuteNonQuery();

            if (atualizou)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'UPDATE',
                        'TB_ALUNO',
                        (CAST(@MATRICULA AS VARCHAR)
                        + ' | ' + CAST(@ID_ESTADO AS VARCHAR)
                        + ' | ' + UPPER(@NOME)
                        + ' | ' + CONVERT(VARCHAR, @DATA_NASCIMENTO, 103)
                        + ' | ' + UPPER(@CPF)
                        + ' | ' + UPPER(@RG)
                        + ' | ' + UPPER(@ORGAO_EXPEDITOR_RG)
                        + ' | ' + UPPER(@UF_RG)
                        + ' | ' + UPPER(@DATA_EXPEDICAO_RG)
                        + ' | ' + UPPER(@EMAIL)
                        + ' | ' + UPPER(@PAI)
                        + ' | ' + UPPER(@MAE)),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@MATRICULA", SqlDbType.Int, aluno.Matricula);
                db.AddParam("@ID_ESTADO", SqlDbType.Int, aluno.CodEstado);
                db.AddParam("@NOME", SqlDbType.VarChar, aluno.Nome);
                db.AddParam("@DATA_NASCIMENTO", SqlDbType.DateTime, aluno.DataNascimento); 
                db.AddParam("@CPF", SqlDbType.VarChar, aluno.Cpf);
                db.AddParam("@RG", SqlDbType.VarChar, aluno.Rg);
                db.AddParam("@ORGAO_EXPEDITOR_RG", SqlDbType.VarChar, aluno.OrgaoExpedidor);
                db.AddParam("@UF_RG", SqlDbType.VarChar, aluno.UfRg);
                db.AddParam("@DATA_EXPEDICAO_RG", SqlDbType.DateTime, aluno.DataExpedicaoRg);
                db.AddParam("@EMAIL", SqlDbType.VarChar, aluno.Email);
                db.AddParam("@PAI", SqlDbType.VarChar, aluno.Pai);
                db.AddParam("@MAE", SqlDbType.VarChar, aluno.Mae);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return atualizou;
        }
        #endregion

        #region Excluir Aluno
        public bool excluirAluno(Aluno aluno, string usuario)
        {
            string SQL = @"DELETE 
                            FROM   TB_ALUNO
                            WHERE  MATRICULA = @MATRICULA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, aluno.Matricula);
            //return db.ExecuteNonQuery();

            bool excluiu = db.ExecuteNonQuery();

            if (excluiu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'DELETE',
                        'TB_ALUNO',
                        (CAST(@MATRICULA AS VARCHAR)
                        + ' | ' + CAST(@ID_ESTADO AS VARCHAR)
                        + ' | ' + UPPER(@NOME)
                        + ' | ' + CONVERT(VARCHAR, @DATA_NASCIMENTO, 103)
                        + ' | ' + UPPER(@CPF)
                        + ' | ' + UPPER(@RG)
                        + ' | ' + UPPER(@ORGAO_EXPEDITOR_RG)
                        + ' | ' + UPPER(@UF_RG)
                        + ' | ' + UPPER(@DATA_EXPEDICAO_RG)
                        + ' | ' + UPPER(@EMAIL)
                        + ' | ' + UPPER(@PAI)
                        + ' | ' + UPPER(@MAE)),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@MATRICULA", SqlDbType.Int, aluno.Matricula);
                db.AddParam("@ID_ESTADO", SqlDbType.Int, aluno.CodEstado);
                db.AddParam("@NOME", SqlDbType.VarChar, aluno.Nome);
                db.AddParam("@DATA_NASCIMENTO", SqlDbType.DateTime, aluno.DataNascimento); 
                db.AddParam("@CPF", SqlDbType.VarChar, aluno.Cpf);
                db.AddParam("@RG", SqlDbType.VarChar, aluno.Rg);
                db.AddParam("@ORGAO_EXPEDITOR_RG", SqlDbType.VarChar, aluno.OrgaoExpedidor);
                db.AddParam("@UF_RG", SqlDbType.VarChar, aluno.UfRg);
                db.AddParam("@DATA_EXPEDICAO_RG", SqlDbType.DateTime, aluno.DataExpedicaoRg);
                db.AddParam("@EMAIL", SqlDbType.VarChar, aluno.Email);
                db.AddParam("@PAI", SqlDbType.VarChar, aluno.Pai);
                db.AddParam("@MAE", SqlDbType.VarChar, aluno.Mae);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return excluiu;
        }
        #endregion

        #region Carregar dados do Aluno
        public Aluno carregarDadosAluno(int pMatricula)
        {
            #region Script
            string SQL = @"SELECT ta.MATRICULA,
                               ta.ID_ESTADO,
                               ta.NOME,
                               ta.DATA_NASCIMENTO,
                               ta.CPF,
                               ta.RG,
                               ta.ORGAO_EXPEDITOR_RG,
                               ta.UF_RG,
                               ta.DATA_EXPEDICAO_RG,
                               ta.NACIONALIDADE,
                               ta.NATURALIDADE,
                               ta.UF_NATURALIDADE,
                               ta.GENERO,
                               ta.ESTADO_CIVIL,
                               ta.EMAIL,
                               ta.DDD_TELEFONE,
                               ta.TELEFONE,
                               ta.DDD_CELULAR,
                               ta.CELULAR,
                               ta.PAI,
                               ta.MAE,
                               ta.ENDERECO,
                               ta.CEP,
                               ta.NUMERO,
                               ta.COMPLEMENTO,
                               ta.BAIRRO,
                               ta.UF_ENDERECO,
                               ta.CIDADE,
                               ta.PROFISSAO,
                               -- ta.CURSO_GRADUACAO,
                               -- ta.ANO_CONCLUSAO,
                               -- ta.INSTITUICAO
                               ta.SITUACAO
                        FROM   TB_ALUNO ta
                        WHERE ta.MATRICULA = @MATRICULA";
            #endregion
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, pMatricula);
            try
            {
                Aluno alu = new Aluno();
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                if (dr.Read())
                {
                    //alu.AnoConclusao = Convert.ToInt32(dr["ANO_CONCLUSAO"].ToString());
                    alu.Bairro = dr["BAIRRO"].ToString();
                    alu.Celular = dr["CELULAR"].ToString();
                    alu.Cidade = dr["CIDADE"].ToString();
                    alu.CodEstado = Convert.ToInt32(dr["ID_ESTADO"].ToString());
                    alu.Complemento = dr["COMPLEMENTO"].ToString();
                    alu.Cpf = dr["CPF"].ToString();
                    //alu.CursoGraduacao = dr["CURSO_GRADUACAO"].ToString();
                    if (dr["DATA_EXPEDICAO_RG"] != null)
                        alu.DataExpedicaoRg = Convert.ToDateTime(dr["DATA_EXPEDICAO_RG"].ToString());
                    else
                        alu.DataExpedicaoRg = DateTime.MinValue;

                    if (dr["DATA_NASCIMENTO"] != null && dr["DATA_NASCIMENTO"] != "")
                        alu.DataNascimento = Convert.ToDateTime(dr["DATA_NASCIMENTO"]);
                    else
                        //alu.DataNascimento = DateTime.MinValue;
                        alu.DataNascimento = null;

                    alu.Dddcel = dr["DDD_CELULAR"].ToString();
                    alu.DddTel = dr["DDD_TELEFONE"].ToString();
                    alu.Email = dr["EMAIL"].ToString();
                    alu.Endereco = dr["ENDERECO"].ToString();
                    alu.Cep = dr["CEP"].ToString();
                    alu.EstadoCivil = dr["ESTADO_CIVIL"].ToString();
                    alu.Genero = dr["GENERO"].ToString();
                    //alu.Instituicao = dr["INSTITUICAO"].ToString();
                    alu.Mae = dr["MAE"].ToString();
                    alu.Matricula = pMatricula;
                    alu.Nacionalidade = dr["NACIONALIDADE"].ToString();
                    alu.Naturalidade = dr["NATURALIDADE"].ToString();
                    alu.Nome = dr["NOME"].ToString();
                    if (dr["NUMERO"] != null)
                        alu.Numero = Convert.ToInt32(dr["NUMERO"].ToString());
                    else
                        alu.Numero = 0;
                    alu.OrgaoExpedidor = dr["ORGAO_EXPEDITOR_RG"].ToString();
                    alu.Pai = dr["PAI"].ToString();
                    alu.Profissao = dr["PROFISSAO"].ToString();
                    alu.Rg = dr["RG"].ToString();
                    alu.Situacao = dr["SITUACAO"].ToString();
                    alu.Telefone = dr["TELEFONE"].ToString();
                    alu.UfEndereco = dr["UF_ENDERECO"].ToString();
                    alu.UfNaturalidade = dr["UF_NATURALIDADE"].ToString();
                    alu.UfRg = dr["UF_RG"].ToString();
                }
                return alu;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Verifica se o CPF informado já foi cadastrado
        public bool cpfJaCadastrado(string pCPF)
        {
            string SQL = @"SELECT ta.MATRICULA
                            FROM   TB_ALUNO ta
                            WHERE  ta.CPF = @CPF";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@CPF", SqlDbType.VarChar, pCPF);
            return db.GetDataTable().Rows.Count > 0;
        }
        #endregion

        #region Get Titulações
        public DataTable getTitulacoes(int matricula)
        {
            string SQL = @"SELECT   tat.ID_ALUNO_TITULACAO,
                                    tat.MATRICULA,
                                    tnt.DESCRICAO            AS NIVEL,
                                    tat.DESCRICAO_TITULACAO  AS TITULO,
                                    tat.ANO_CONCLUSAO,
                                    tat.INSTITUICAO,
                                    tat.SITUACAO
                            FROM   TB_ALUNO_TITULACAO tat
                                    JOIN TB_NIVEL_TITULACAO tnt
                                        ON  tnt.ID_NIVEL_TITULACAO = tat.ID_NIVEL_TITULACAO
                            WHERE  tat.MATRICULA = @MATRICULA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, matricula);
            return db.GetDataTable();
        }
        #endregion

        #region Existe Titulação
        public DataTable existeTitulacao(int matricula, string descTitulacao, int nivelTitulacao, int anoConclusao, string instituicao)
        {        
            string SQL = @"SELECT ID_ALUNO_TITULACAO
                            FROM   TB_ALUNO_TITULACAO
                                        WHERE  MATRICULA = @MATRICULA and
                                        UPPER(DESCRICAO_TITULACAO) = UPPER(@DESCRICAO_TITULACAO) and
                                        ANO_CONCLUSAO = @ANO_CONCLUSAO and
                                        UPPER(INSTITUICAO) = UPPER(@INSTITUICAO) ";

                    DataAccess db = new DataAccess(SQL);

                    db.AddParam("@MATRICULA", SqlDbType.Int, matricula);
                    db.AddParam("@DESCRICAO_TITULACAO", SqlDbType.VarChar, descTitulacao);
                    db.AddParam("@ID_NIVEL_TITULACAO", SqlDbType.Int, nivelTitulacao); 
                    db.AddParam("@ANO_CONCLUSAO", SqlDbType.Int, anoConclusao);
                    db.AddParam("@INSTITUICAO", SqlDbType.VarChar, instituicao);
                    return db.GetDataTable();
        }
        #endregion
        
        #region Incluir Titulação
        public bool incluirTitulacao(Titulacao titulacao)
        {
            string SQL = @"INSERT INTO TB_ALUNO_TITULACAO
                                  (
                                    MATRICULA,
                                    ID_NIVEL_TITULACAO,
                                    DESCRICAO_TITULACAO,
                                    ANO_CONCLUSAO,
                                    INSTITUICAO,
                                    SITUACAO,
                                    DATA_CADASTRO
                                  )
                                VALUES
                                  (
                                    @MATRICULA,
                                    @ID_NIVEL_TITULACAO,
                                    @DESCRICAO_TITULACAO,
                                    @ANO_CONCLUSAO,
                                    @INSTITUICAO,
                                    @SITUACAO,
                                    GETDATE()
                                  )";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@MATRICULA", SqlDbType.Int, titulacao.MatriculaAluno);
            db.AddParam("@ID_NIVEL_TITULACAO", SqlDbType.Int, titulacao.CodNivelTitulacao);
            db.AddParam("@DESCRICAO_TITULACAO", SqlDbType.VarChar, titulacao.Titulo);
            db.AddParam("@ANO_CONCLUSAO", SqlDbType.Int, titulacao.AnoConclusao);
            db.AddParam("@INSTITUICAO", SqlDbType.VarChar, titulacao.Instituicao);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, titulacao.Situacao);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Alterar Titulação
        public bool alterarTitulacao(Titulacao titulacao)
        {
            string SQL = @"UPDATE TB_ALUNO_TITULACAO
                            SET    ID_NIVEL_TITULACAO             = @ID_NIVEL_TITULACAO,
                                   DESCRICAO_TITULACAO            = @DESCRICAO_TITULACAO,
                                   ANO_CONCLUSAO                  = @ANO_CONCLUSAO,
                                   INSTITUICAO                    = @INSTITUICAO,
                                   SITUACAO                       = @SITUACAO
                            WHERE  ID_ALUNO_TITULACAO     = @ID_ALUNO_TITULACAO";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_ALUNO_TITULACAO", SqlDbType.Int, titulacao.Codigo);
            db.AddParam("@ID_NIVEL_TITULACAO", SqlDbType.Int, titulacao.CodNivelTitulacao);
            db.AddParam("@DESCRICAO_TITULACAO", SqlDbType.VarChar, titulacao.Titulo);
            db.AddParam("@ANO_CONCLUSAO", SqlDbType.Int, titulacao.AnoConclusao);
            db.AddParam("@INSTITUICAO", SqlDbType.VarChar, titulacao.Instituicao);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, titulacao.Situacao);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Get Dados Titulação
        public Titulacao getDadosTitulacao(int idAlunoTitulacao)
        {
            string SQL = @"SELECT tat.ID_ALUNO_TITULACAO,
                                   tat.MATRICULA,
                                   tat.ID_NIVEL_TITULACAO,
                                   tat.DESCRICAO_TITULACAO,
                                   tat.ANO_CONCLUSAO,
                                   tat.INSTITUICAO,
                                   tat.SITUACAO
                            FROM   TB_ALUNO_TITULACAO tat
                            WHERE  tat.ID_ALUNO_TITULACAO = @ID_ALUNO_TITULACAO";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_ALUNO_TITULACAO", SqlDbType.Int, idAlunoTitulacao);
            try
            {
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                Titulacao titulacao = new Titulacao();
                if (dr.Read())
                {
                    titulacao.Codigo = Convert.ToInt32(dr["ID_ALUNO_TITULACAO"]);
                    titulacao.AnoConclusao = Convert.ToInt32(dr["ANO_CONCLUSAO"]);
                    titulacao.CodNivelTitulacao = Convert.ToInt32(dr["ID_NIVEL_TITULACAO"]);
                    titulacao.MatriculaAluno = Convert.ToInt32(dr["MATRICULA"]);
                    titulacao.Instituicao = dr["INSTITUICAO"].ToString();
                    titulacao.Situacao = dr["SITUACAO"].ToString();
                    titulacao.Titulo = dr["DESCRICAO_TITULACAO"].ToString();
                }
                return titulacao;
            }
            catch
            {
                return null;
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Excluir Titulação
        public bool excluirTitulacao(int idAlunoTitulacao)
        {
            string SQL = @"DELETE 
                            FROM   TB_ALUNO_TITULACAO
                            WHERE  ID_ALUNO_TITULACAO = @ID_ALUNO_TITULACAO";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_ALUNO_TITULACAO", SqlDbType.Int, idAlunoTitulacao);
            return db.ExecuteNonQuery();
        }
        #endregion
    }
}
